#!/bin/bash

dt="`date -d "$d  0 days ago "  "+%Y-%m-%d"`"
mysql -uroot -proot -hhadoop2<<EOF
use sqlserver;
SELECT SUBSTR(SENDTIME,1,16),trim(STATIONCODE),
sum(case WHEN trim(OILCODE) ='92#' THEN 1 ELSE 0 END )AS mumber92,
SUM(CASE WHEN trim(OILCODE) ='92#' THEN LITRE/1000 ELSE 0 END) AS LITRE92,
SUM(CASE WHEN trim(OILCODE) ='92#' THEN REALAMOUNT/100 ELSE 0 END)AS MONEY92,
sum(case WHEN trim(OILCODE) ='95#' THEN 1 ELSE 0 END )AS mumber95,
SUM(CASE WHEN trim(OILCODE) ='95#' THEN LITRE/1000 ELSE 0 END) AS LITRE95,
SUM(CASE WHEN trim(OILCODE) ='95#' THEN REALAMOUNT/100 ELSE 0 END)AS MONEY95,
sum(case WHEN trim(OILCODE) ='97#' THEN 1 ELSE 0 END )AS mumber97,
SUM(CASE WHEN trim(OILCODE) ='97#' THEN LITRE/1000 ELSE 0 END) AS LITRE97,
SUM(CASE WHEN trim(OILCODE) ='97#' THEN REALAMOUNT/100 ELSE 0 END)AS MONEY97,
sum(case WHEN trim(OILCODE) ='0#' THEN 1 ELSE 0 END )AS mumber0,
SUM(CASE WHEN trim(OILCODE) ='0#' THEN LITRE/1000 ELSE 0 END) AS LITRE0,
SUM(CASE WHEN trim(OILCODE) ='0#' THEN REALAMOUNT/100 ELSE 0 END)AS MONEY0,
sum(case WHEN trim(OILCODE) ='-10#' THEN 1 ELSE 0 END )AS mumber_10,
SUM(CASE WHEN trim(OILCODE) ='-10#' THEN LITRE/1000 ELSE 0 END) AS LITRE_10,
SUM(CASE WHEN trim(OILCODE) ='-10#' THEN REALAMOUNT/100 ELSE 0 END)AS MONEY_10,
sum(case WHEN trim(OILCODE) ='-20#' THEN 1 ELSE 0 END )AS mumber_20,
SUM(CASE WHEN trim(OILCODE) ='-20#' THEN LITRE/1000 ELSE 0 END) AS LITRE_20,
SUM(CASE WHEN trim(OILCODE) ='-20#' THEN REALAMOUNT/100 ELSE 0 END)AS MONEY_20,
sum(case WHEN trim(OILCODE)='98#' THEN 1 ELSE 0 END )AS mumber98,
SUM(CASE WHEN trim(OILCODE)='98#' THEN LITRE/1000 ELSE 0 END) AS LITRE98,
SUM(CASE WHEN trim(OILCODE)='98#' THEN REALAMOUNT/100 ELSE 0 END)AS MONEY98,
sum(case WHEN trim(OILCODE)='80#' THEN 1 ELSE 0 END )AS mumber80,
SUM(CASE WHEN trim(OILCODE)='80#' THEN LITRE/1000 ELSE 0 END) AS LITRE80,
SUM(CASE WHEN trim(OILCODE)='80#' THEN REALAMOUNT/100 ELSE 0 END)AS MONEY80
FROM realtime.TBL_ORDERDETAILS_ALL WHERE OILCODE IS NOT NULL and ORDER_DEAL = 4 and SUBSTR(SENDTIME,1,10)='$dt'
GROUP BY SUBSTR(SENDTIME,1,16),STATIONCODE;

EOF